package it.evento;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import java.util.LinkedList;
import java.util.List;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.ejb.LocalBean;
import java.util.Date;

/**
 *
 * @author Ilo
 */
@Stateless
@LocalBean
public class BusinessLogic {
    @EJB
    private UsersFacade usersFacade;
    @EJB
    private UsertypesFacade userstypesFacade;
    @EJB
    private EventsFacade eventsFacade;
    @EJB
    private CommentsFacade commentsFacade;
    @EJB
    private LocationFacade locationFacade;
    @EJB
    private EventtypesFacade eventtypesFacade;
    @EJB
    private EventsfeedbackFacade eventsfeedbackFacade;

    
    
    // [USERTYPES] //
    /**
     * 
     * @return tutti gli Usertypes
     */
    public List<Usertypes> getAllUsertypes(){
        return userstypesFacade.findAll();
    }
    
    public Usertypes getUserTypeById(int id) {
        String sql = "SELECT t "
                + "FROM Usertypes t "
                + "WHERE t.id=" + id;

        try {
            return (Usertypes) usersFacade.personalQuery(sql).getSingleResult();
        } catch (Exception ex) {
            return null;
        }
    }
    // [/ USERTYPES] //
    
    // [USERS] //
    /**
     * Gets all the users from the database
     * 
     * @return a list representing all the users
     */
    public List<Users> getAllUsers() {
        return usersFacade.findAll();
    }
    
    /**
     * Gets all registered users from the database
     * 
     * @return a list representing all registered users
     */
    public List<Users> getRegistered() {
        List<Users> list = new LinkedList<>();
        
        for(Users user : usersFacade.findAll()) {
            if(user.getType() == 3)     // userType=3  => Registered
                list.add(user);
        }
        
        return list;
    }
    
    /**
     * Gets all publishers from the database
     * 
     * @return a list representing all publishers
     */
    public List<Users> getPublishers() {
        List<Users> list = new LinkedList<>();
        
        for(Users user : usersFacade.findAll()) {
            if(user.getType() == 2)     // userType=2  => Publisher
                list.add(user);
        }
        
        return list;
    }
    
    /**
     * Gets all administrators from the database
     * 
     * @return a list representing all administrators
     */
    public List<Users> getAdministrators() {
        List<Users> list = new LinkedList<>();
        
        for(Users user : usersFacade.findAll()) {
            if(user.getType() == 1)     // userType=1  => Administrator
                list.add(user);
        }
        
        return list;
    }
    
    /**
     * 
     * @param name è l'username
     * @return l'oggetto user desiderato
     */
    public Users getUser(String name){          
        for(Users user : usersFacade.findAll()) {
            if((user.getUsername()).equals(name))     
                return user;
        }
        
        return null;
    }
    /**
     * 
     * @param userId
     * @return un oggetto User che rappresenta l'utente
     */
    public Users getUser(int userId){          
        for(Users user : usersFacade.findAll()) {
            if((user.getId()).equals(userId))     
                return user;
        }
        
        return null;
    }
    

    /**
     * Gets the user from the ID
     *
     * @param id the ID of the user
     * @return the user with that id
     */
    public Users getUserById(int id) {
        return usersFacade.find(id);
    }
    
    /**
     * Updates the user password
     * 
     * @param id the ID of the user
     * @param pwd the new password
     */
    public void updatePassword(int id, String pwd) {
        Users u = usersFacade.find(id);
        u.setPassword(pwd);
        usersFacade.edit(u);
    }
    
    /**
     * Updates the user infos
     * 
     * @param id the ID of the user
     */
    public void updateUser(int id, Users newUser) {
        newUser.setId(id);
        usersFacade.edit(newUser);
    }
    
    /**
     * Executes the user login
     * 
     * @param name the name of the user
     * @param pwd the password of the user
     * @return a User instance for the logged in user, null otherwise
     */
    public Users loginUser(String name, String pwd) {
        String sql = "SELECT u "
                    + "FROM Users u "
                      + "WHERE u.username=\"" + name + "\" "
                      + "AND u.password=\"" + pwd + "\"";
        
        try {
            return (Users)usersFacade.personalQuery(sql).getSingleResult();
        }
        catch(Exception ex) {
            return null;
        }
    }
    
    public boolean insertUsers(String name, String surname, Date birth, String company, String info, String username, String pwd, int publisher){
        boolean tro=false;
        for(Users user : usersFacade.findAll()) {
            if((user.getUsername()).equals(username)){
                tro=true;
            }
        }
        
        if(!tro){
            Users u= new Users(name,surname,birth,username,pwd);

            if(!company.equals(""))
               u.setCompanyname(company);
            if(!info.equals(""))
                u.setInfo(info);
            u.setUpgrade(publisher);
            
            usersFacade.create(u);

            System.out.println("BusinessLogic insertUser avvenuta");
            return true;
        }
        return false;
            
    }
    /*
    public void upgradeUser(String username){
        Users current=getUser(username);
        current.setUpgrade("true");
        usersFacade.edit(current);
    }*/
    
    public boolean removeUser(int userId){
        try{
            usersFacade.remove(getUser(userId));
        }catch(Exception e){
            System.out.println(e);
            return false;
        }
        return true;
    }
    /**
     * 
     * @param idEvent
     * @param name
     * @param description
     * @param location
     * @param type
     * @param start
     * @param end
     * @return true se l'utente è stato modificato, false altrimenti
     */
    public boolean  modifyUser(String idUser, String username, String name, String surname, String birth, String company, int type, int upgrade){
       String sql = "UPDATE Users "
               + "SET username= '" + username + "', name= '" + name + "', surname= '" + surname + "', birth= '" + birth + "', companyname= '" + company + "', type= " + type + ", upgrade= " + upgrade + " "
               + "WHERE id= " + idUser;
       System.out.println("SQL: " + sql);
        
       try {
           usersFacade.personalQuery(sql).executeUpdate();
       } catch (Exception ex) {
           System.out.println(ex);
           return false;
       }
       return true;
   }
    // [/USERS] //
    
    // [EVENTS] //
    /**
     * Gets all the events
     * 
     * @return a list of all the events
     */
    public List<Events> getAllEvents() {
        return eventsFacade.findAll();
    }
    
    public List<Events> getOnlyTypeEvents(int type){
        List<Events> all=getAllEvents();
        List<Events> result=new LinkedList<Events>();
        for(Events e:all){
            if(e.getType()==type)
                result.add(e);       
        }
        
        return result;
    }
    /*
    * remove selected event
    */
    public boolean removeEvent(int eventId){
        try{
            eventsFacade.remove(getEvent(eventId));
        }catch(Exception e){
            System.out.println(e);
            return false;
        }
        return true;
    }
    
    
    
     /**
     * Gets all the events upload from a input user
     * @param publisher current username
     * @return a list of all the events
     */
    public List<Events> getUserEvents(String publisher){
        List<Events> l= new LinkedList<Events>();
       
        for(Events e:eventsFacade.findAll()){
            if(e.getPublisher().equals(publisher))
                l.add(e);
        }
            
        return l;
    }
    
    /**
     * Gets the last <max> events
     * 
     * @param max the threshold
     * @return a list of last <max> events
     */
    public List<Events> getLastEvents(int max) {
        String sql = "SELECT e "
                    + "FROM Events e "
                      + "ORDER BY e.startdate";
        
        try {
            // Gestire il range 0-max
            return eventsFacade.personalQuery(sql).getResultList();
        }
        catch(Exception ex) {
            return null;
        }
    }
    
    /**
     * Gets the last 10 events
     * 
     * @return a list of last 10 events
     */
    public List<Events> getLastEvents() {
        return getLastEvents(10);
    }
    
    /**
     * Gets the top <max> favourite events
     * 
     * @param max the threshold
     * @param userId the id of the user
     * @return a list of top <max> favourite events
     */
    public List<Events> getFavouriteEvents(int userId, int max) {
        String sql = "SELECT e "
                    + "FROM Events e, Preferences p "
                        + "WHERE e.id = p.preferencesPK.idevent "
                        + "AND p.preferencesPK.iduser = " + userId + " "
                        + "ORDER BY e.startdate";
                 
        try {
            return eventsFacade.personalQuery(sql).getResultList();
        }
        catch(Exception ex) {
            return null;
        }
    }
    
    /**
     * Gets the top 10 favourite events
     * 
     * @param userId the id of the user
     * @return a list of top 10 favourite events
     */
    public List<Events> getFavouriteEvents(int userId) {
        return getFavouriteEvents(userId, 10);
    }
    
    /**
     * Gets a single event
     * 
     * @param eventId the id of the event
     * @return the selected event
     */
    public Events getEvent(int eventId) {
        return eventsFacade.find(eventId);
    }
    /**
     * 
     * @param eventName 
     * @return list of events having the same name 
     */
    public List<Events> getEvent(String eventName){      
        String sql = "SELECT e "
                   + "FROM Events e "
                     + "WHERE e.name=\"" + eventName + "\"";
        System.out.println(sql);
        return eventsFacade.personalQuery(sql).getResultList();  
    }

    /**
     * Gets the events of the passed ids
     *
     * @param ids an array of all the ids to find
     * @return a list representing selected events
     */
    public List<Events> getEvents(String ids) {
        if (ids.equals("")) {
            return getAllEvents();
        }

        String sql = "SELECT e "
                + "FROM Events e "
                + "WHERE e.id IN (" + ids + ")";

        try {
            return locationFacade.personalQuery(sql).getResultList();
        } catch (Exception ex) {
            return null;
        }
    }
    
    
    /**
     * 
     * @param idType
     * @param idLoc
     * @param start
     * @param end
     * @param match
     * @return List respect parameter of advanced search
     * @throws ParseException 
     */
    public List<Events> advancedSearchEvent(int idType, int idLoc, String start, String end, String match) throws ParseException{
        
        if(idType==-1 && idLoc==-1 && start.equals("") && end.equals("") && match.equals(""))
            return getAllEvents();
        
        String sql = "SELECT e "
              + "FROM Events e "
                + "WHERE ";
        boolean primo=false;//c'è già il primo parametro
        if(idType!=-1){
            sql+= "e.type= " + idType ;
            primo=true;
        }
        if(idLoc!=-1){
            if(primo){
                sql+= " AND e.location= " + idLoc ;
            }else{
                primo=true;
                sql+= " e.location= " + idLoc ;
            }
                
        }
        SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//formatta la da per il DB
        if(!start.equals("")){
            Date startD = new SimpleDateFormat("MM/dd/yyyy").parse(start);
            
            start=DATE_FORMAT.format(startD);
            if(primo){
                sql+= " AND e.startdate >= \"" + start + "\"";
            }else{
                primo=true;
                sql+= " e.startdate >= \"" + start + "\"";
            }
        }
        if(!end.equals("")){
            Date endD = new SimpleDateFormat("MM/dd/yyyy").parse(end);
            end=DATE_FORMAT.format(endD);
            if(primo){
                sql+= " AND e.enddate <= \"" + end + "\"";
            }else{
                primo=true;
                sql+= " e.enddate <= \"" + end + "\"";
            }
        }
        if(!match.equals("")){
            if(primo){
                sql+= " AND e.name LIKE \"%" + match + "%\"";
            }else{
                primo=true;
                sql+= " e.name LIKE \"%" + match + "%\"";
            }
            
        }
        System.out.println(sql);
        return eventsFacade.personalQuery(sql).getResultList();  
    }
    
   /**
     * Insert event into DB
     * @param startdate
     * @param description
     * @param enddate
     * @param location
     * @param publisher
     * @param type
     * @param name
    */
   public void insertEvent(String name,String description, Date startdate, Date enddate, int location, String publisher, int type,String link){
        if(!name.equals("") && !publisher.equals("")){
            /*Per caricare dati sul DB bisogna creare un nuovo oggetto tramite Facade*/
            System.out.println("description: "+description);
            Events e=new Events(name,description,startdate,enddate,location,publisher,type);
            e.setEsternallink(link);//valutare se tenere i link..
            eventsFacade.create(e);
        }  
    }
 
   public boolean  modifyEvent(String idEvent, String name, String description, int location, int type, String start, String end){
       String sql = "UPDATE Events "
               + "SET name= '" + name + "', description= '" + description + "', location= " + location + ", type= " + type + ", startdate= '" + start + "', enddate= '" + end + "' "
               + "WHERE id= " + idEvent;
       System.out.println("SQL: " + sql);
       try {
           eventsFacade.personalQuery(sql).executeUpdate();
       } catch (Exception ex) {
           System.out.println(ex);
           return false;
       }
       return true;
   }

    // [/EVENTS] //
    
    // [COMMENTS] //
    /**
     * Creates a new comment for the specified event by the specified user
     * 
     * @param idEvent the id of the event
     * @param idUser the id of the user
     * @param comment the text of the comment
     */
    public void insertComment(int idEvent, int idUser, String comment, Date date) {
        commentsFacade.create(new Comments(idEvent, idUser, comment, date));
    }
    
    public List<Comments> getComment(int idEvent){
        List<Comments> all=commentsFacade.findAll();
        LinkedList<Comments> ris= new LinkedList<Comments>();
        for(Comments c:all){
            if(c.getIdevent()==idEvent){
                ris.add(c);
            }
        }
        return ris;
    }
    // [/COMMENTS] //
    
     // [LOCATIONS] //
    /**
     * Gets all locations from the database
     *
     * @return a list representing all locations
     */
    public List<Location> getAllLocations(){
        return locationFacade.findAll();
    }
    /**
     * Find if a location exist
     * @param locationName
     * @return id of location if them exist, -1 else
     */
    public int locationId(String locationName){
        String sql = "SELECT l "
                   + "FROM Location l "
                     + "WHERE l.name=\"" + locationName + "\"";
        System.out.println(sql);
        List<Location> list=locationFacade.personalQuery(sql).getResultList();
        if(list.isEmpty())
            return -1;
       
        return list.get(0).getId();
    }
    /**
     * Insert a new location on the DB
     * @param locationName
     * @param address
     * @param Description
     * @return ID of create location
     */
    public int insertLocation(String locationName,String address,String Description){
        Location l=new Location(locationName);
        l.setAddress(address);
        l.setDescription(Description);
        locationFacade.create(l);
       
        return l.getId();
    }

    /**
     * Gets the locations of the passed ids
     *
     * @param ids an array of all the ids to find
     * @return a list representing selected locations
     */
    public List<Location> getEventLocations(int[] ids) {
        String strIds = "";
        for (int id : ids) {
            if (strIds.equals("")) {
                strIds += id;
            } else {
                strIds += ", " + id;
            }
        }

        return getEventLocations(strIds);
    }

    /**
     * Gets the locations of the passed ids
     *
     * @param ids an array of all the ids to find
     * @return a list representing selected locations
     */
    public List<Location> getEventLocations(String ids) {
        if (ids.equals("")) {
            return getAllLocations();
        }

        String sql = "SELECT l "
                + "FROM Location l "
                + "WHERE l.id IN (" + ids + ")";

        try {
            return locationFacade.personalQuery(sql).getResultList();
        } catch (Exception ex) {
            return null;
        }
    }
    // [/LOCATIONS] //

    // [EVENTTYPES] //
    /**
     * Gets all the event's types from the database
     *
     * @return a list representing all the enevent's types
     */
    public List<Eventtypes> getAllEventTypes() {
        return eventtypesFacade.findAll();
    }
    // [/EVENTTYPES] //
    
    // [EVENTSFEEDBACK] //
    public List<Eventsfeedback> getAllEventsFeedback() {
        return eventsfeedbackFacade.findAll();
    }
    /**
     * Gets the list of feedback for a specified event
     * 
     * @param eid the ID of the event
     * @return the list of event's feedbacks
     */
    public List<Eventsfeedback> getEventsFeedback(int eid) {
        String sql = "SELECT ef "
                   + "FROM Eventsfeedback ef "
                     + "WHERE ef.idevent=" + eid;
        
        return eventsfeedbackFacade.personalQuery(sql).getResultList();
    }
    
    /**
     * Tests if the user has already rated the event
     * 
     * @param eid the ID of the event
     * @param uid the ID of the user
     * @return true if the user has rated the event, false otherwise
     */
    public boolean hasEventFeedback(int eid, int uid) {
        String sql = "SELECT ef "
                   + "FROM Eventsfeedback ef "
                     + "WHERE ef.idevent=" + eid + " "
                     + "AND ef.iduser=" + uid;
        
        return eventsfeedbackFacade.personalQuery(sql).getResultList().size()>0;
    }
    
    /**
     * Inserts a new feedback entry
     * @param ef the new feedback to be inserted
     */
    public void insertFeedback(Eventsfeedback ef) {
        eventsfeedbackFacade.create(ef);
    }
    // [/EVENTSFEEDBACK] //
    
    // [COMMENTS] //
    /**
     * Creates a new comment for the specified event by the specified user
     *
     * @param idEvent the id of the event
     * @param idUser the id of the user
     * @param comment the text of the comment
     */
    public void insertComment(int idEvent, int idUser, String comment) {
        commentsFacade.create(new Comments(idEvent, idUser, comment, null));
    }
    // [/COMMENTS] //
   
}
